from flask.blueprints import Blueprint
from flask import request,make_response
from flask import current_app as app
from flask.json import jsonify
import json
import os
import uuid
from utiles.myfile import myfile

#导入文件
from flask import Blueprint
from xlrd import open_workbook

#后天导出文件
from io import BytesIO
from xlrd import open_workbook
from urllib.parse import quote
# import xlsxwriter
import datetime
import mimetypes

bp_users = Blueprint('bp_users',__name__)


# 导入excel
@bp_users.route('/upload', methods=['POST'])
def uploads():
    print(request.files)
    fileilename = request.files['file']
    print(request.files['file'])
    fileilename.save(os.path.join('./static/' + fileilename.filename))
    name = 'static/' + fileilename.filename
    bk = open_workbook(name, encoding_override='utf-8')
    sh = bk.sheets()[0]
    nrows = sh.nrows
    ncols = sh.ncols
    # 行
    for i in range(2, nrows):
        row_date = sh.row_values(i)
        for x in range(0, ncols):
            print(row_date[x])
    return 'ok'

# 导出exls
@bp_users.route('/download', methods=["GET", "POST"])
def download():
    # 实例化二进制数据
    out = BytesIO()
    # 创建一个Excel实例
    workbook = xlsxwriter.Workbook(out)
    # 添加一个sheet，add_worksheet()，默认"Sheet1"，可自定义命名，add_worksheet("资产列表")
    table = workbook.add_worksheet()
    # table.write(行, 列, 字段名)
    # 行、列从 0 开始计数
    # 字段命名根据每个人使用情况而定，插入数据部分也是
    table.write(0, 0, "资产编号")
    table.write(0, 1, "资产名称")

    workbook.close()
    filename = quote("资产列表_{}.xlsx".format(datetime.datetime.now().strftime("%Y%m%d%H%M%S")))
    file = make_response(out.getvalue())
    out.close()
    mime_type = mimetypes.guess_type('%s.xlsx' % filename)[0]
    file.headers['Content-Type'] = mime_type
    file.headers["Cache-Control"] = "no-cache"
    file.headers['Content-Disposition'] = "attachment; filename*=utf-8''{}".format(filename)
    return file

# 获取无限极
@bp_users.route('/acporgn', methods=["GET"])
def acporgn():
    # msg = request.form.get('msg')
    # print(msg)
    # db = Dd()
    # if msg != None:
    #     sql = "select * from `organ` where name like '%s'" % msg
    #     print('1515')
    #     orgn = db.get_one(sql)
    #     if orgn['level'] == 0:
    #         orgn['label'] = orgn['name']
    #         orgn['children'] = []
    #         print(orgn)
    #         return orgn
    #     if orgn['level'] != 0:
    #         sql = "select * from `organ` where id='%s'" % orgn['level']
    #         data = db.get_one(sql)
    #         print(data)
    #         print(orgn)
    # else:
        sql = "select * from `organ`"
        db = Dd()
        orgn = db.get_all(sql)
        if not orgn:
            return orgn
        #  先把列表重组成字典
        dict1 = {}
        list1 = []
        for i in orgn:
            i['label'] = i['name']
            dict1[i['id']] = i
        for j in orgn:
            level = j['level']
            j['label']=j['name']
            #  pid等于0，就是顶级，
            if level == 0:
                # 直接加入列表
                list1.append(j)
            else:
                #  查看父类是否有子类
                if 'children' not in dict1[level]:
                    dict1[level]['children'] = []
                dict1[level]['children'].append(j)

        return {'code':'200','list1':list1}

# #  无限极分类数据重构展示
#

# 添加数据
@bp_users.route('/reliable_orgn',methods=['POST'])
def reliable_orgn():
    name=request.form.get('name')
    codtype=request.form.get('codtype')
    sort=request.form.get('sort')
    type=request.form.get('type')
    id=request.form.get('id')
    print(id)
    if type==1:
        level=0
        db = Dd()
        orgn = db.get_all("select * from `organ` where level=0")
        orgn=len(orgn)
        coding='AO'+str(orgn+1)
        print(coding)
        sql="insert into  `organ` (name,coding,sort,level,codtype) values ('%s','%s',%s,%s,%s)" % (name,coding,sort,level,codtype)
        db.insert(sql)
        return {'code': '200', 'msg': '添加成功'}
    else:
        level = id
        db = Dd()
        data = db.get_one("select * from `organ` where id=%s" % id)
        orgn = db.get_all("select * from `organ` where level=%s" % id)
        orgn = len(orgn)
        print(data)
        coding = data['coding']+'AO' + str(orgn + 1)
        sql = "insert into  `organ` (name,coding,sort,level,codtype) values ('%s','%s',%s,%s,%s)" % (
        name, coding, sort, level, codtype)
        db.insert(sql)
        return {'code':'200','msg':'添加成功'}

# 获取详情
@bp_users.route('/reliable_orgn',methods=['GET'])
def reliable_or():
    id=request.args.get('id')
    db = Dd()
    orgn = db.get_one("select * from `organ` where level=%s" % id)
    names=orgn['name']
    return {'code': '200', 'names': names}

from se import Myes
import json
@bp_users.route('/esget')
def esget():
    es = Myes('testtabel','test-type')
    body = {
        # match 是单个
        #    "match":{
        #     "name":"abc"
        # }
        # multi_match是双个类型来筛选
        "multi_match":{
            "query":"abc",
            "fields":["name", "content"]
        }
    }
    data = es.search(body)
    address_data = data['hits']['hits']
    print(address_data)
    return 'ok'

@bp_users.route('/estestinsert')
def estest():
    es = Myes('testtabel','test-type')
    try:
        db=Dd()
        sql = "select * from test limit 0,10"
        data = db.get_all(sql)
        j=-1
        for i in data:
            j+=1
            es.insert(i)
        return 'true'
    except:
        print('error')
        return 'false'




@bp_users.route('/get_userguan',methods=['GET'])
def get_userguan():
    page=request.args.get('page')
    print(page)
    size=10
    db=Dd()
    page=(int(page)-1)*size
    sql = "select * from user limit %s,10" % page
    list=db.get_all(sql)
    return {'code':'200','list':list}


@bp_users.route('/post_user',methods=['POST'])
def post_user():
    username=request.form.get('username')
    password=request.form.get('password')
    passwords=request.form.get('passwords')
    phone=request.form.get('phone')
    email=request.form.get('email')
    jobnum=request.form.get('jobnum')
    geter=request.form.get('geter')
    name=request.form.get('name')
    print(username)
    if password!=passwords:
        return {'code': '403', 'msg': '确认密码错误'}
    db=Dd()
    sql="insert into  `user` (username,password,name,phone,email,jobnum,geter) values ('%s','%s','%s',%s,'%s',%s,%s)" % (username,password,name,phone,email,jobnum,geter)
    db.insert(sql)
    return {'code': '200', 'msg': '添加成功'}

